PROC SQL queries against non SAS data sources that trigger implicit pass-through and contain a WHERE or HAVING clause might return an incorrect number of rows. Incorrect results have been verified against SAS/ACCESS^® Interface to Oracle and SAS/ACCESS^® Interface to ODBC libraries, but an incorrect number of rows could potentially be returned with other SAS/ACCESS engines.

See the Full Code tab for example code that generates an incorrect result set.

There are two workarounds. You can use either the LIBNAME option DIRECT_SQL=NOGENSQL or use the PROC SQL NOIPASSTHRU option. Use of either option produces a correct result set.

A hot fix is planned for this issue.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	Base SAS	z/OS	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		z/OS 64-bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft® Windows® for x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8 Enterprise 32-bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8 Enterprise x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8 Pro 32-bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8 Pro x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8.1 Enterprise 32-bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8.1 Enterprise x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8.1 Pro 32-bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 8.1 Pro x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows 10	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows Server 2012 Datacenter	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows Server 2012 R2 Datacenter	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows Server 2012 R2 Std	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows Server 2012 Std	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows Server 2016	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Microsoft Windows Server 2019	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Enterprise 32 bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Enterprise x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Home Premium 32 bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Home Premium x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Professional 32 bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Professional x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Ultimate 32 bit	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Windows 7 Ultimate x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		64-bit Enabled AIX	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		64-bit Enabled Solaris	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		HP-UX IPF	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Linux for x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8
		Solaris for x64	9.4_M6	9.4_M8	9.4 TS1M6	9.4 TS1M8

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This is an example of a LIBNAME and PROC SQL query that can generate the incorrect result set.

---

libname oralib oracle path=abc user=xxxx pass=yyyy preserve_names=yes CONNECTION = GLOBAL INSERTBUFF = 15000 DBCOMMIT = 0 READBUFF = 
15000 UPDATEBUFF = 15000 DIRECT_SQL = YES DB_LENGTH_SEMANTICS_BYTE = NO SQL_FUNCTIONS = ALL DIRECT_EXE = DELETE DBMAX_TEXT=6000;

PROC SQL;
CREATE TABLE WORK.ABC AS 
SELECT 
	t1.XXXX, 
	t1.YYYY, 
	(SUM(t1.ZZZZ)) FORMAT=COMMAX20.2 AS ZZZZ
FROM (
	SELECT 
		t1.XXXX, 
 		t1.YYYY, 
        t2.aaa,
		(SUM(t1.ZZZZ)) FORMAT=COMMAX20.2 AS ZZZZ
    FROM oralib.table1 t1
    INNER JOIN oralib.table2 t2 ON (t1.join_col = t2.join_col)
	WHERE t2.bbb = 'Z1'
	GROUP BY t1.XXXX, t1.YYYY , aaa  
) t1
GROUP BY t1.XXXX, t1.YYYY
HAVING (CALCULATED ZZZZ) > 0 ;
QUIT;

PROC SQL queries run against Oracle and ODBC database tables return an incorrect number of rows when the query contains a HAVING clause.

Type:	Problem Note
Priority:	high

Date Modified:	2021-05-20 15:31:56
Date Created:	2021-05-14 13:15:38